<?php

// Dies entspricht error_reporting(E_ALL);
ini_set('error_reporting', E_ALL);

//require_once 'databaseConnect.class.php';
require_once dirname(__FILE__) . './databaseConnect.php';
require_once 'secretHash.php';

/**
 * SQL Functions for myJoka API.
 * This class will have CRUD methods for database tables
 *
 * @author Kamdem Kenmogne
 */
class DatabaseHandler {

    private $database;
    private $statement;

    // constructor
    function __construct() {
        $this->database = new DatabaseConnect(); //message DatabaseFunctions();
    }

    /**
     * Create message user
     * returns user details
     * @author Kamdem Kenmogne
     */
    public function createUser($username, $email, $dontUseThispassword, $birthdate) 
    {  
        
        $response = array();
        if (!$this->isUserNameExisted($username)) {
            // First check if user already existed in db
            if (!$this->isUserExisted($email)) {
                // Generating password hash
                $password_hash = PassHash::hash($dontUseThispassword);
                // Generating API key
                $api_key = $this->generateApiKey();
                //Generating Datetime
                $date_now = date('Y-m-d H:i:s');
                // insert query
                //$this->database->beginTransaction();
                $this->statement = $this->database->prepare("INSERT INTO users(username, email,"
                        . " password, api_key, birthdate, created_at, updated_at) values(:username,:email, "
                        . ":password, :api_key, :birthdate, :created_at, :updated_at)");
                $this->statement->bindParam(':username', $username);
                $this->statement->bindParam(':email', $email);
                $this->statement->bindParam(':password', $password_hash);
                $this->statement->bindParam(':api_key', $api_key);
                $this->statement->bindParam(':birthdate', $birthdate);
                $this->statement->bindParam(':created_at', $date_now);
                $this->statement->bindParam(':updated_at', $date_now);
                $result = $this->statement->execute();
                $this->database->close();
                // Check for successful insertion
                if ($result) {
                    return USER_CREATED_SUCCESSFULLY; // User successfully inserted
                } else {
                    return USER_CREATE_FAILED; // Failed to create user
                }
            } else {
                return USER_ALREADY_EXISTED; // User with same email already existed in the db
            }
        } else {
            return USERNAME_ALREADY_EXISTED;
        }

        return $response;
    }

    /**
     * Checking user login
     * @param String $email User login email id
     * @param String $clean_password User login password
     * @return boolean User login status success/fail
     */
    public function checkLogin($email, $clean_password) {
//        $this->database->beginTransaction();
        $this->statement = $this->database->prepare("SELECT password FROM users WHERE email = :email");
        $this->statement->bindParam(':email', $email);
        //
        $this->statement->execute();
        //
        $no_of_rows = $this->statement->rowCount();

        if ($no_of_rows > 0) {

            $password = $this->statement->fetch(PDO::FETCH_ASSOC);

            if (PassHash::check_password($password['password'], $clean_password)) {
                // User password is correct
                return TRUE;
            } else {
                // user password is incorrect
                return FALSE;
            }
        } else {
            // user not existed with the email
            return FALSE;
        }
        $this->database->close();
    }
    
    /**
     * Fetching user by email
     * @author Kamdem Kenmogne
     * @param String $email User email id
     */
    public function getUserByEmail($email) 
    {
//        $this->database->beginTransaction();
        $this->statement = $this->database->prepare("SELECT username, email, "
                ."api_key, created_at FROM users WHERE email = :email");
        $this->statement->bindParam(':email', $email);
        if ($this->statement->execute()){
            $user = $this->statement->fetch(PDO::FETCH_ASSOC);
            return $user;
        } 
        else{
            return NULL;
        }
        $this->database->close();
    }
    /**
     * Fetching all user 
     * @param String $email User email id
     */
    public function getUsers() 
    {
//        $this->database->beginTransaction();
        $this->statement = $this->database->prepare("SELECT * FROM users");
        if ($this->statement->execute()){
            $user = $this->statement->fetchAll(PDO::FETCH_ASSOC);
            return $user;
        } 
        else{
            return NULL;
        }
        $this->database->close();
    }
    
    /**
     * Fetching user api key
     * @author Kamdem Kenmogne
     * @param String $user_id user id primary key in user table
     */
    public function getApiKeyById($user_id) 
    {
//        $this->database->beginTransaction();
        $this->statement = $this->database->prepare("SELECT api_key FROM Users WHERE user_Id = :id");
        $this->statement->bindParam(":i", $user_id);
        if ($this->statement->execute()) {
            $api_key = $this->statement->fetch(PDO::FETCH_ASSOC);
            return $api_key['api_key'];
        } else {
            return NULL;
        }
        $this->database->close();
    }
    
    /**
     * Fetching user id by api key
     * @author Kamdem Kenmogne
     * @param String $api_key user api key
     */
    public function getUserId($api_key) 
    {
//        $this->database->beginTransaction();
        $this->statement = $this->database->prepare("SELECT user_Id FROM users WHERE api_key = :api_key");
        $this->statement->bindParam(":api_key", $api_key);
        if ($this->statement->execute()) {
            $user_id = $this->statement->fetch(PDO::FETCH_ASSOC);
            return $user_id['user_Id'];
        } else {
            return NULL;
        }
        $this->database->close();
    }
    
    public function getUserMail($api_key) 
    {
//        $this->database->beginTransaction();
        $this->statement = $this->database->prepare("SELECT email FROM users WHERE api_key = :api_key");
        $this->statement->bindParam(":api_key", $api_key);
        if ($this->statement->execute()) {
            $user_email = $this->statement->fetch(PDO::FETCH_ASSOC);
            return $user_email['email'];
        } else {
            return NULL;
        }
        $this->database->close();
    }
    
     /**
     * Validating user api key
     * If the api key is there in db, it is a valid key
     * @author Kamdem Kenmogne
     * @param String $api_key user api key
     * @return boolean
     */
    public function isValidApiKey($api_key) 
    {
//        $this->database->beginTransaction();
        $this->statement = $this->database->prepare("SELECT user_Id from users WHERE api_key = :api_key");
        $this->statement->bindParam(":api_key", $api_key);
        $this->statement->execute();
        $tempVal = $this->statement->fetch(PDO::FETCH_ASSOC);
        $num_rows = $this->statement->rowCount();
        $this->database->close();
        return $num_rows > 0;
        
    }
    
    /**
     * Generating random Unique MD5 String for user Api key
     */
    private function generateApiKey() 
    {
        return md5(uniqid(rand(), true));
    }
    
    /**
     * Get user by email and password.
     * @author Kamdem Kenmogne
     */
    public function getUserByEmailAndPassword($email, $password) 
    {
//        $this->database->beginTransaction();
        $this->statement = $this->database->query('SELECT * FROM users WHERE '
                . 'email = :mail');
        $this->statement->bindParam(':mail', $email);
        $this->statement->execute();
        $this->database->close();

        $resultUsers = $this->statement->rowCount();

        if ($resultUsers > 0) {
            $result = $this->statement->fetchAll(PDO::FETCH_ASSOC);
            $salt = $result['salt'];
            $encrypted_password = $result['encrypted_password'];
            $hash = $this->checkhashSSHA($salt, $password);

            // check for password equality
            if ($encrypted_password == $hash) {
                // user authentication details are correct
                return $result;
            }
        } else {
            // user not found
            return false;
        }
    }

    /**
     * Check user is existed or not.
     * @author Kamdem Kenmogne
     */
    public function isUserExisted($email) 
    {
        //echo '--isUserExisted--';
        try {
            //$this->database->beginTransaction();
            $this->statement = $this->database->prepare('SELECT email FROM users '
                    . 'WHERE email = :mail');
            $this->statement->bindParam(':mail', $email);
            $this->statement->execute();
            $no_of_rows = $this->statement->rowCount();
            if ($no_of_rows > 0) {
                // user existed
                return true;
            } else {
                // user not existed
                return false;
            }
            //$this->database->close();
        } catch (Exception $ex) {
            print "Error!: " . $ex->getMessage() . "<br/>";
        }
    }
    
    /**
     * Check username is existed or not.
     * @author Kamdem Kenmogne
     */
    public function isUserNameExisted($username) 
    {
        //echo '--isUserExisted--';
        try {
            //$this->database->beginTransaction();
            $this->statement = $this->database->prepare('SELECT username FROM users '
                    . 'WHERE username = :username');
            $this->statement->bindParam(':username', $username);
            $this->statement->execute();
            $no_of_rows = $this->statement->rowCount();
            if ($no_of_rows > 0) {
                // user existed
                return true;
            } else {
                // user not existed
                return false;
            }
            //$this->database->close();
        } catch (Exception $ex) {
            print "Error!: " . $ex->getMessage() . "<br/>";
        }
    }
    
    /* ------------- `Articles` table method ------------------ */

    
    public function createArticle($user_id, $categorie_Id, $name, $description, $image)
    {
//        $this->database->beginTransaction();
        //Generating Datetime
        $createDate = date('Y-m-d H:i:s');
        $updateDate = date('Y-m-d H:i:s');
        
        $this->statement = $this->database->prepare("INSERT INTO articles(categorie_Id, name,"
            ."description, image, created_at, updated_at)"
            ."values(:categorie_Id, :name, :description, :image,"
            .":created_at, :updated_at)");
        $this->statement->bindParam(':categorie_Id', $categorie_Id);
        $this->statement->bindParam(':name', $name);
        $this->statement->bindParam(':description', $description);
        $this->statement->bindParam(':image', $image); 
        $this->statement->bindParam(':created_at', $createDate);
        $this->statement->bindParam(':updated_at', $updateDate);
        try{
            $result = $this->statement->execute();
            if ($result) {
            // article row created
            // now assign the article to user
            $message_article_id = $this->database->lastInsertId();//insert_id;
            $res = $this->createUserArticle($user_id, $message_article_id);
            if ($res) {
                // article created successfully
                return $message_article_id;
            } else {
                return NULL; // article failed to create
            }
        } else{
            return NULL; // article failed to create
        }
        $this->database->close();
            
        } catch (Exception $ex) {
            print "Error!: " . $ex->getMessage() . "<br/>";
        }
        
    }
    
    /**
     * Fetching all user 
     * @param String $email User email id
     */
    public function getAllArticles() 
    {
//        $this->database->beginTransaction();
        $this->statement = $this->database->prepare("SELECT * FROM articles");
        if ($this->statement->execute()){
            $article = $this->statement->fetchAll(PDO::FETCH_ASSOC);
            return $article;
        } 
        else{
            return NULL;
        }
        $this->database->close();
    }
    
    /**
     * Fetching Artickes by categorie _Id
     * @author Kamdem Kenmogne
     * @param String $categorie_Id 
     */
    public function getArticleByCat($categorie_Id) 
    {
//        $this->database->beginTransaction();
        $this->statement = $this->database->prepare("SELECT *  FROM articles"
                . " WHERE categorie_Id = :categorie_Id");
        $this->statement->bindParam(':categorie_Id', $categorie_Id);
        if ($this->statement->execute()){
            $categorie = $this->statement->fetchAll(PDO::FETCH_ASSOC);
            return $categorie;
        } 
        else{
            return NULL;
        }
        $this->database->close();
    }

    /**
     * Fetching single article
     * @param String $article_id id of the article
     */
    public function getArticle($article_id) 
    {
        
        $this->statement = $this->database->prepare("SELECT * FROM articles"
                . " WHERE article_Id = :article_Id");
        $this->statement->bindParam(":article_Id", $article_id);
        if ($this->statement->execute()) {
            // TODO
            $res = $this->statement->fetch(PDO::FETCH_ASSOC);
            $this->database->close();
            return $res;
        } else {
            return NULL;
        }
    }
    
//    public function getArticle( $user_id, $article_id) 
//    {
//        
//        $this->statement = $this->database->prepare("SELECT t.categorie_Id, t.name,"
//                . " t.description, t.image, t.created_at, t.updated_at"
//                . " from articles t, user_articles ua"
//                . " WHERE t.article_Id = :article_Id AND ua.article_Id = t.article_Id AND ua.user_Id = :user_Id");
//        $this->statement->bindParam(":user_Id", $user_id);
//        $this->statement->bindParam(":article_Id", $article_id);
//        if ($this->statement->execute()) {
//            // TODO
//            $res = $this->statement->fetch(PDO::FETCH_ASSOC);
//            $this->database->close();
//            return $res;
//        } else {
//            return NULL;
//        }
//    }

    /**
     * Fetching all user articles
     * @param String $user_id id of the user
     */
    public function getAllUserArticles($user_id) 
    {
        $this->statement = $this->database->prepare("SELECT t.* FROM articles t, "
                . "user_articles ua WHERE t.article_Id = ua.article_Id AND ua.user_Id = :user_Id ");
        $this->statement->bindParam(":user_Id", $user_id);
        if($this->statement->execute()){
            $articles = $this->statement->fetchAll(PDO::FETCH_ASSOC);
            return $articles;
        }else {
            return NULL;
        }
        $this->database->close();   
    }

    /**
     * Updating article
     * @param String $article_id id of the article
     * @param String $article article text
     * @param String $status article status
     */
    public function updateArticle($user_id, $article_id, $categorie_Id, $name, $description, $image) 
    {
        $updateDate = date('Y-m-d H:i:s');
        $this->statement = $this->database->prepare("UPDATE articles t, user_articles ua "
            . "set t.categorie_Id = :categorie_Id, t.name = :name, t.description = :description, "
            . "t.updated_at = :updated_at, t.image = :image "
            . " WHERE t.article_Id = $article_id  AND t.article_Id = ua.article_Id AND ua.user_Id = $user_id");
        
        $this->statement->bindParam(':categorie_Id', $categorie_Id);
        $this->statement->bindParam(':name', $name);
        $this->statement->bindParam(':description', $description);
        if($image != ""){
          $this->statement->bindParam(':image', $image);   
        }
        $this->statement->bindParam(':updated_at', $updateDate); 
        $this->statement->execute();
        $num_affected_rows = $this->statement->rowCount();
        $this->database->close();
        return $num_affected_rows > 0;
    }

    /**
     * Deleting a article
     * @param String $article_id id of the article to delete
     */
    public function deleteArticle($user_id, $article_id) 
    {
        $this->statement = $this->database->prepare("DELETE t FROM articles t, "
                . "user_articles ua WHERE t.article_Id = :article_Id "
                . "AND ua.article_Id = t.article_Id AND ua.user_Id = :user_Id");
        $this->statement->bindParam(":user_Id", $user_id);
        $this->statement->bindParam(":article_Id", $article_id);
        $this->statement->execute();
        $num_affected_rows = $this->statement->rowCount();
        $this->database->close();
        return $num_affected_rows > 0;
    }

    /* ------------- `user_articles` table method ------------------ */

    /**
     * Function to assign a article to user
     * @param String $user_id id of the user
     * @param String $article_id id of the article
     */
    public function createUserArticle( $user_id, $article_id) 
    { 
        $this->statement = $this->database->prepare("INSERT INTO user_articles "
                ." ( user_Id, article_Id) values(:user_Id, :article_Id)");
        $this->statement->bindParam(":user_Id", $user_id);
         $this->statement->bindParam(":article_Id", $article_id);

        $result = $this->statement->execute();

        if (!$result) {
            die('execute() failed: ' . htmlspecialchars($this->statement->error));
        }
//        $this->statement->closeCursor();
        return $result;
    }
    
    /* ------------- `categories` table method ------------------ */
    
    public function createCategorie($name, $description) 
    {  
        $response = array();
        
         if (!$this->isCategorieExisted($name)) {
            //Generating Datetime
            $date_now = date('Y-m-d H:i:s');  
            // insert query
            //$this->database->beginTransaction();
            $this->statement = $this->database->prepare("INSERT INTO categories(name,"
                    . " description, created_at, updated_at) values(:name, "
                    . ":description, :created_at, :updated_at)");
            $this->statement->bindParam(':name', $name);
            $this->statement->bindParam(':description', $description);
            $this->statement->bindParam(':created_at', $date_now); 
            $this->statement->bindParam(':updated_at', $date_now);
            $result = $this->statement->execute();
 
            // Check for successful insertion
            if ($result) {                
                return CATEGORIE_CREATED_SUCCESSFULLY;// User successfully inserted
            } else {                
                return CATEGORIE_CREATE_FAILED;// Failed to create user
            }
         }else{
             return CATEGORIE_ALREADY_EXISTED;
         }
        return $response;
    }
    
     /**
     * Fetching all categorie
     * @param 
     */
    public function getCategories() 
    {
//        $this->database->beginTransaction();
        $this->statement = $this->database->prepare("SELECT * FROM categories");
        if ($this->statement->execute()){
            $categorie = $this->statement->fetchAll(PDO::FETCH_ASSOC);
            return $categorie;
        } 
        else{
            return NULL;
        }
        $this->database->close();
    }
    
    /**
     * 
     * @param type $categorie_Id
     * @param type $name
     * @param type $description
     * @return type
     */
    public function updateCategorie($categorie_Id, $name, $description) 
    {
        $updateDate = date('Y-m-d H:i:s');
        $this->statement = $this->database->prepare("UPDATE categorie"
            . "set name = :name, description = :description, "
            . " WHERE categorie_Id = $categorie_Id ");
                
        $this->statement->bindParam(':name', $name);
        $this->statement->bindParam(':description', $description);
        $this->statement->bindParam(':updated_at', $updateDate); 
        $this->statement->execute();
        
        $num_affected_rows = $this->statement->rowCount();
        $this->database->close();
        return $num_affected_rows > 0;
    }
    
    /**
     * Check categorie is existed or not.
     * @author Kamdem Kenmogne
     */
    public function isCategorieExisted($name) 
    {
        //echo '--isUserExisted--';
        try {
            //$this->database->beginTransaction();
            $this->statement = $this->database->prepare('SELECT name FROM categories '
                    . 'WHERE name = :name');
            $this->statement->bindParam(':name', $name);
            $this->statement->execute();
            $no_of_rows = $this->statement->rowCount();
            if ($no_of_rows > 0) {
                // user existed
                return true;
            } else {
                // user not existed
                return false;
            }
            //$this->database->close();
        } catch (Exception $ex) {
            print "Error!: " . $ex->getMessage() . "<br/>";
        }
    }
    
    /*******************************Message***********************/
    
    public function sendNews($article_Id, $sendermail, $object, $message)
    {
        $this->statement = $this->database->prepare('SELECT u.email FROM users u, user_articles ua '
                    . 'WHERE ua.user_Id = u.user_Id AND ua.article_Id = :article_Id');
        $this->statement->bindParam(':article_Id', $article_Id);
        $this->statement->execute();
        $mail = $this->statement->fetch(PDO::FETCH_ASSOC);

        $receiverMail = $mail ["email"];
        
        $senddate = date('Y-m-d H:i:s');
        $this->statement = $this->database->prepare("INSERT INTO messages(sender ,"
            . "receiver, object, message, sendDate)"
            ."values(:sender, :receiver, :object, :message, :sendDate)");
        $this->statement->bindParam(':sender', $sendermail);
        $this->statement->bindParam(':receiver', $receiverMail);
        $this->statement->bindParam(':object', $object);
        $this->statement->bindParam(':message', $message);
        $this->statement->bindParam(':sendDate', $senddate);
        $result = $this->statement->execute();
        $this->database->close();
        if ($result) {
            return true;
        } else {
            return false; // article failed to create
        }
             
    }
    
    public function getAllMessages($user_Id) 
    {
        $this->statement = $this->database->prepare("SELECT n.* FROM messages n, "
                . "users u WHERE n.receiver = u.email AND u.user_Id = :user_Id ");
        $this->statement->bindParam(":user_Id", $user_Id);
        if($this->statement->execute()){
            $messages = $this->statement->fetchAll(PDO::FETCH_ASSOC);
            return $messages;
        }else {
            return NULL;
        }
        $this->database->close();   
    }
    
    public function getMessage($user_Id, $messages_Id)
    {
        $this->statement = $this->database->prepare("SELECT n.* FROM messages n,"
                ."users u WHERE n.receiver = u.email AND u.user_Id = :user_Id "
                . "AND n.messages_Id = :messages_Id");
        $this->statement->bindParam(":messages_Id", $messages_Id);
        $this->statement->bindParam(":user_Id", $user_Id);
        if ($this->statement->execute()) {
            // TODO
            $res = $this->statement->fetch(PDO::FETCH_ASSOC);
            $this->database->close();
            return $res;
        } else {
            return NULL;
        }
    }
    
    public function deletedMessage($user_mail, $messages_Id)
    {
        $this->statement = $this->database->prepare("DELETE n FROM messages n, "
                . "users u WHERE n.receiver = u.email AND u.email = :email"
                . "AND n.messages_Id = :messages_Id");
        $this->statement->bindParam(":email", $user_mail);
        $this->statement->bindParam(":messages_Id", $messages_Id);
        $this->statement->execute();
        $num_affected_rows = $this->statement->rowCount();
        $this->database->close();
        return $num_affected_rows > 0;
    }
    
    /***************************WishList*****************************/
    
    public function createWish($user_Id, $searchText) {
        
        $createdate = date('Y-m-d H:i:s');
        $this->statement = $this->database->prepare("INSERT INTO wish_list(user_Id ,"
            . "searchText, created_at)"
            ."values(:user_Id, :searchText, :created_at)");
        $this->statement->bindParam(':user_Id', $user_Id);
        $this->statement->bindParam(':searchText', $searchText);
        $this->statement->bindParam(':created_at', $createdate);
        try {
             $result = $this->statement->execute();
        $this->database->close();
        } catch (Exception $ex) {
            print "Error!: " . $ex->getMessage() . "<br/>";
        }
       
        if ($result) {
            return true;
        } else {
            return false; // article failed to create
        }
    }
    
    public function getWishList($user_Id) {
        
        $this->statement = $this->database->prepare("SELECT w.* FROM wish_list w, "
                . "users u WHERE w.user_Id = u.user_Id AND u.user_Id = :user_Id ");
        $this->statement->bindParam(":user_Id", $user_Id);
        if($this->statement->execute()){
            $messages = $this->statement->fetchAll(PDO::FETCH_ASSOC);
            return $messages;
        }else {
            return NULL;
        }
        $this->database->close();   
    }
    
    public function deletedWish($user_Id, $search_Id) {
        
        $this->statement = $this->database->prepare("DELETE w FROM wish_list w, "
                . "users u WHERE w.user_Id = u.user_Id AND u.user_Id = :user_Id "
                . "AND w.search_Id = :search_Id");
        $this->statement->bindParam(":user_Id", $user_Id);
        $this->statement->bindParam(":search_Id", $search_Id);
        $this->statement->execute();
        $num_affected_rows = $this->statement->rowCount();
        $this->database->close();
        return $num_affected_rows > 0;
    }
    
    public function getAllWishs() {
        
        $this->database->beginTransaction();
        $this->statement = $this->database->prepare("SELECT * FROM wish_list");
        if ($this->statement->execute()){
            $wish = $this->statement->fetchAll(PDO::FETCH_ASSOC);
            return $wish;
        } 
        else{
            return NULL;
        }
        $this->database->close();
        
    }
    
}